﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Web;
using nkstdgia.common.HoSoThamDinh;
using nkstdgia.common.ThamDinhGia.BusinessLogic;
using nkstdgia.common.ThamDinhGia.BusinessObjects;
using nkstdgia.excel.export;
using wsRK.WindowsUtility;
using nkstdgia.common.DuongKhaoSat;
using System.Data;
using System.Xml;
using System.Globalization;

namespace nkstdgia.main.report
{
    public class ExcelCommand
    {
        #region variable

        private string _filename = "";

        // Total report
        private const int TOTAL_REPORT_START_INDEX = 3; // start at column C
        private const String SEARCH_KEY_TOKEN = "||";
        private const String YEU_TO_SO_SANH = "Tài Sản ";
        private Hashtable totalReportMapRow = new Hashtable();
        //private const COLUMN_WIDTH = 

        private Hashtable giadoanduongReportMapRow = new Hashtable();

        // money unit rate:
        // 1: vnd
        // 1000000: triệu vnd(tvnd)
        private int iMoneyUnitRate = 1;

        #endregion variable

        public ExcelCommand()
        {
            //initialize totalReportMap row
            totalReportMapRow.Add("SEARCH_KEY", "A");
            totalReportMapRow.Add("YEU_TO_SO_SANH", 2);
            totalReportMapRow.Add("DIA_CHI", 3);
            totalReportMapRow.Add("GIA_BAN", 4);
            totalReportMapRow.Add("DTKV", 5);
            totalReportMapRow.Add("GIA_DAT_TLKC", 6);
            totalReportMapRow.Add("GIA_DAT_TDCT", 7);
            totalReportMapRow.Add("DK_THANH_TOAN", 9);
            totalReportMapRow.Add("TT_PHAP_LI", 14);
            totalReportMapRow.Add("VI_TRI", 19);
            totalReportMapRow.Add("LOI_THE_KD", 24);
            totalReportMapRow.Add("HD_THUA_DAT", 29);
            totalReportMapRow.Add("CS_HA_TANG", 34);
            totalReportMapRow.Add("HUONG", 39);

            
           
            if(ConfigurationManager.AppSettings["MoneyUnit"] == "vnd")
                iMoneyUnitRate = 1000000;
            else if(ConfigurationManager.AppSettings["MoneyUnit"] == "tvnd")
                iMoneyUnitRate = 1;
            else
                iMoneyUnitRate = 1;
        }

        public byte[] exportToExcel(int maPKS)
        {
            
            byte[] byteArr = null;

            #region Process excel

            HO_SO_THAM_DINH hstd;
            DUONG_KHAO_SAT dks;
            GIA_THAM_DINH gtd;
            THONG_TIN_VI_TRI ttvt;
            THUA_DAT td;
            CO_SO_HA_TANG csht;
            CONG_TRINH_XAY_DUNG ctxd;
            VAT_LIEU_XAY_DUNG vlxd;
            try
            {
                hstd = ThamDinhGiaFacade.GetHO_SO_THAM_DINHBL().GetByMA_HO_SO(maPKS);
                dks = ThamDinhGiaFacade.GetDUONG_KHAO_SATBL().GetByMA_DKS(hstd.MA_DKS);
                gtd = ThamDinhGiaFacade.GetGIA_THAM_DINHBL().GetByMA_HO_SO(maPKS);
                ttvt = ThamDinhGiaFacade.GetTHONG_TIN_VI_TRIBL().GetByMA_HO_SO(maPKS);
                td = ThamDinhGiaFacade.GetTHUA_DATBL().GetByMA_HO_SO(maPKS);
                csht = ThamDinhGiaFacade.GetCO_SO_HA_TANGBL().GetByMA_HO_SO(maPKS);
                ctxd = ThamDinhGiaFacade.GetCONG_TRINH_XAY_DUNGBL().GetByMA_HO_SO(maPKS);
                vlxd = ThamDinhGiaFacade.GetVAT_LIEU_XAY_DUNGBL().GetByMA_HO_SO(maPKS);
            }
            catch (Exception)
            {
                return null;
            }

            String exportFilePath = copyNewFileFromBKSTemplate();
            if (exportFilePath == null)
                return null;

            SpreedEdit exportHandler = new SpreedEdit(exportFilePath);
            if (!exportHandler.startEditSpreed(ConfigurationManager.AppSettings["ExcelMainSheet"]))
                return null;

            // Start update cells

            #region update hstd
            /*
             *update hstd properties
             * 
             */
            if (hstd != null)
            {
                exportHandler.updateCellValue("B", 2, hstd.SO_PHIEU);
                exportHandler.updateCellValue("B", 8, hstd.TEN_CHU_SU_DUNG);
                exportHandler.updateCellValue("B", 9, hstd.DIA_CHI);
                if (hstd.TINH_TRANG_PHAP_LY == 3) // id giay huong trong bang tham so
                    exportHandler.updateCellValue("B", 10, 1);
                else if (hstd.TINH_TRANG_PHAP_LY == 4) // id giay do trong bang tham so
                    exportHandler.updateCellValue("B", 10, 2);
                else if (hstd.TINH_TRANG_PHAP_LY == 5) // id giay hong trong bang tham so
                    exportHandler.updateCellValue("B", 10, 3);
                else if (hstd.TINH_TRANG_PHAP_LY == 6) // id giay to khac trong bang tham so
                {
                    exportHandler.updateCellValue("B", 10, hstd.NOI_DUNG_PHAP_LY);
                }

                if (hstd.LOAI_KHU_VUC_KINH_DOANH == 0) // khong co
                    exportHandler.updateCellValue("B", 76, 1);
                else if (hstd.LOAI_KHU_VUC_KINH_DOANH == 1) // trung binh
                    exportHandler.updateCellValue("B", 76, 2);
                else if (hstd.LOAI_KHU_VUC_KINH_DOANH == 2) // sam uat
                    exportHandler.updateCellValue("B", 76, 3);

                // Update y kien tham dinh vien
                /* riahk fix */
                hstd.Y_KIEN_THAM_DINH = hstd.Y_KIEN_THAM_DINH.Replace("<br />\n", "\n");
                hstd.Y_KIEN_THAM_DINH = hstd.Y_KIEN_THAM_DINH.Replace("<br />", "\n");

                exportHandler.updateCellValue("A", 103, hstd.Y_KIEN_THAM_DINH);
                exportHandler.updateCellValue("B", 106, hstd.NGAY_KHOI_TAO);

                if (hstd.GIA_DAT_1 <= 0)
                    exportHandler.updateCellValue("B", 110, 0);
                else
                    exportHandler.updateCellValue("B", 110, hstd.GIA_DAT_1 * iMoneyUnitRate);

                if (hstd.GIA_DAT_BINH_QUAN_1 <= 0)
                    exportHandler.updateCellValue("C", 110, 0);
                else
                    exportHandler.updateCellValue("C", 110, hstd.GIA_DAT_BINH_QUAN_1 * iMoneyUnitRate);

                if (hstd.GIA_DAT_2 <= 0)
                    exportHandler.updateCellValue("B", 111, 0);
                else
                    exportHandler.updateCellValue("B", 111, hstd.GIA_DAT_2 * iMoneyUnitRate);

                if (hstd.GIA_DAT_BINH_QUAN_2 <= 0)
                    exportHandler.updateCellValue("C", 111, 0);
                else
                    exportHandler.updateCellValue("C", 111, hstd.GIA_DAT_BINH_QUAN_2 * iMoneyUnitRate);
            }
            #endregion update hstd

            #region update dks
            /*
             * Update dks properties
             */
            if (dks != null)
            {
                THAM_SO ts = ThamDinhGiaFacade.GetTHAM_SOBL().GetByMA_THAM_SO(dks.QUAN);
                //temp
                exportHandler.updateCellValue("B", 3, ts.NOI_DUNG);
                //temp
                ts = ThamDinhGiaFacade.GetTHAM_SOBL().GetByMA_THAM_SO(dks.PHUONG);
                exportHandler.updateCellValue("B", 4, ts.NOI_DUNG);

                exportHandler.updateCellValue("B", 5, dks.DUONG);
                exportHandler.updateCellValue("B", 6, dks.DOAN_DUONG);
            }
            #endregion update dks


            #region update gtd
            /*
             * Update gtd properties
             */
            if (gtd != null)
            {
                //trieu or cay vang
                if (gtd.GIA_GIAO_DICH_CAY_VANG > 0)
                {
                    exportHandler.updateCellValue("B", 11, gtd.GIA_GIAO_DICH_CAY_VANG);
                    exportHandler.updateCellValue("C", 11, "Cây Vàng");
                }
                else
                {
                    exportHandler.updateCellValue("B", 11, gtd.GIA_GIAO_DICH_TRIEU_DONG);
                    exportHandler.updateCellValue("C", 11, "Triệu Đồng");
                }

                exportHandler.updateCellValue("B", 12, gtd.THOI_DIEM_GIAO_DICH);

                if (gtd.LOAI_THANH_TOAN == 7) //lay tu bang THAM SO : Tra ngay
                    exportHandler.updateCellValue("B", 13, 1);
                else if (gtd.LOAI_THANH_TOAN == 8) //lay tu bang THAM SO : Tra cham
                    exportHandler.updateCellValue("B", 13, 2);
                else if (gtd.LOAI_THANH_TOAN == 9) //lay tu bang THAM SO : Khac
                {
                    exportHandler.updateCellValue("B", 13, gtd.NOI_DUNG_THANH_TOAN);
                }


            }
            #endregion update gtd

            
            #region update ttvt
            if (ttvt != null)
            {
                exportHandler.updateCellValue("B", 73, ttvt.CACH_TRUNG_TAM);

                exportHandler.updateCellValue("B", 74, ttvt.CACH_DUONG_CHINH);

            }
            #endregion update ttvt
            
              
            
            #region update td
          
            if (td != null)
            {
                exportHandler.updateCellValue("B", 16, td.SO_THUA_DAT);
                exportHandler.updateCellValue("B", 17, td.TO_BAN_DO);
                exportHandler.updateCellValue("B", 19, td.CHIEU_DAI);
                exportHandler.updateCellValue("B", 20, td.CHIEU_RONG);
                exportHandler.updateCellValue("B", 21, td.TONG_DIEN_TICH);
                exportHandler.updateCellValue("B", 23, td.DIEN_TICH_CONG_NHAN);
                exportHandler.updateCellValue("B", 24, td.DIEN_TICH_KHONG_CONG_NHAN);
                exportHandler.updateCellValue("B", 25, td.DIEN_TICH_SU_DUNG_CHUNG);

                if (td.LOAI_HINH_DANG_THUA_DAT == 10) //lay tu bang tham so : Vuong
                    exportHandler.updateCellValue("B", 26, 1);
                else if (td.LOAI_HINH_DANG_THUA_DAT == 11) //lay tu bang tham so : Top
                    exportHandler.updateCellValue("B", 26, 2);
                else if (td.LOAI_HINH_DANG_THUA_DAT == 12) //lay tu bang tham so : No hau
                    exportHandler.updateCellValue("B", 26, 3);
                else if (td.LOAI_HINH_DANG_THUA_DAT == 13)  //lay tu bang tham so : Khac
                {
                    exportHandler.updateCellValue("B", 26, td.HINH_DANG_THUA_DAT);
                }

                if (td.LOAI_DUONG_PHO == 14) // mat tien
                {
                    exportHandler.updateCellValue("B", 29, td.LO_GIOI);
                    try
                    {
                        int iSoMatTienDuong = Int32.Parse(td.SO_MAT_TIEN);
                        exportHandler.updateCellValue("B", 30, Int32.Parse(td.SO_MAT_TIEN));
                    }
                    catch (Exception e)
                    {

                    }
                    if (td.LOAI_LAN_DUONG == 16)
                        exportHandler.updateCellValue("B", 31, 1);             //lay tu bang tham so : mot chieu
                    else if (td.LOAI_LAN_DUONG == 17)
                        exportHandler.updateCellValue("B", 31, 2);            //lay tu bang tham so : hai chieu
                    else if (td.LOAI_LAN_DUONG == 18)
                        exportHandler.updateCellValue("B", 31, 3);             //lay tu bang tham so : co dai phan cach
                     
                }
                else if (td.LOAI_DUONG_PHO == 15)
                {
                    /*
                    if (td.LOAI_HEM == 19)
                        exportHandler.updateCellValue("B", 32, 1);             //lay tu bang tham so : hem cap 1
                    else if (td.LOAI_HEM == 20)
                        exportHandler.updateCellValue("B", 32, 2);             //lay tu bang tham so : hem cap 2
                    else if (td.LOAI_HEM == 21)
                        exportHandler.updateCellValue("B", 32, 3);             //lay tu bang tham so : hem cap 3
                    else if (td.LOAI_HEM == 22)
                        exportHandler.updateCellValue("B", 32, 4);             //lay tu bang tham so : hem cap 4
                    */
                    String sTTHem = " ";
                    if (td.LOAI_HEM == 19)
                        sTTHem += "Hẻm cấp 1-Cấp 1";             //lay tu bang tham so : hem cap 1
                    else if (td.LOAI_HEM == 20)
                        sTTHem += "Hẻm cấp 1-Cấp 2";             //lay tu bang tham so : hem cap 2
                    else if (td.LOAI_HEM == 21)
                        sTTHem += "Hẻm cấp 1-Cấp 3";             //lay tu bang tham so : hem cap 3
                    else if (td.LOAI_HEM == 22)
                        sTTHem += "Hẻm cấp 1-Cấp 4";             //lay tu bang tham so : hem cap 4

                    if (td.LOAI_HEM == 23)
                        sTTHem += "Hẻm cấp khác-Cấp 1";            //tu dinh nghia, k co trong bang tham so: hem cap khac cap 1
                    else if (td.LOAI_HEM == 24)
                        sTTHem += "Hẻm cấp khác-Cấp 2";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 2
                    else if (td.LOAI_HEM == 25)
                        sTTHem += "Hẻm cấp khác-Cấp 3";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 3
                    else if (td.LOAI_HEM == 26)
                        sTTHem += "Hẻm cấp khác-Cấp 4";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 4
                    exportHandler.updateCellValue("B", 32, sTTHem);

                }

            }
            #endregion update td
            
            
            #region update csht
            
            if (csht != null)
            {
                //Update Ha tang ki thuat

                if (csht.DONG_HO_DIEN == 1)
                    exportHandler.updateCellValue("B", 80, 1);
                else
                    exportHandler.updateCellValue("B", 80, 2);

                if (csht.CAP_TREO == 1)
                    exportHandler.updateCellValue("B", 81, 1);
                else
                    exportHandler.updateCellValue("B", 81, 2);

                if (csht.CAP_NGAM == 1)
                    exportHandler.updateCellValue("B", 82, 1);
                else
                    exportHandler.updateCellValue("B", 82, 2);

                if (csht.DONG_HO_NUOC == 1)
                    exportHandler.updateCellValue("B", 84, 1);
                else
                    exportHandler.updateCellValue("B", 84, 2);

                if (csht.AP_LUC_NUOC == 1)
                    exportHandler.updateCellValue("B", 85, 1);
                else
                    exportHandler.updateCellValue("B", 85, 2);

                if (csht.CHAT_LUONG_NUOC == 1)
                    exportHandler.updateCellValue("B", 86, 1);
                else
                    exportHandler.updateCellValue("B", 86, 2);

                if (csht.TIENG_ON == 1)
                    exportHandler.updateCellValue("B", 88, 1);
                else
                    exportHandler.updateCellValue("B", 88, 2);

                if (csht.KHOI_BUI == 1)
                    exportHandler.updateCellValue("B", 89, 1);
                else if (csht.KHOI_BUI == 2)
                    exportHandler.updateCellValue("B", 89, 2);
                else
                    exportHandler.updateCellValue("B", 89, 3);

                if (csht.TINH_TRANG_NGAP == 1)
                    exportHandler.updateCellValue("B", 90, 1);
                else if (csht.TINH_TRANG_NGAP == 2)
                    exportHandler.updateCellValue("B", 90, 2);
                else if (csht.TINH_TRANG_NGAP == 3)
                    exportHandler.updateCellValue("B", 90, 3);
                else
                    exportHandler.updateCellValue("B", 90, 4);

                if (csht.HE_THONG_CHIEU_SANG == 1)
                    exportHandler.updateCellValue("B", 92, 1);
                else
                    exportHandler.updateCellValue("B", 92, 2);

                if (csht.CHOT_BAO_VE == 1)
                    exportHandler.updateCellValue("B", 93, 1);
                else
                    exportHandler.updateCellValue("B", 93, 2);

                if (csht.AN_NINH == 1)
                    exportHandler.updateCellValue("B", 94, 1);
                else if (csht.AN_NINH == 2)
                    exportHandler.updateCellValue("B", 94, 2);
                else
                    exportHandler.updateCellValue("B", 94, 3);

                if (csht.GAN_CO_SO_GIAO_DUC == 1)
                    exportHandler.updateCellValue("B", 96, 1);
                else
                    exportHandler.updateCellValue("B", 96, 2);

                if (csht.GAN_CO_SO_Y_TE == 1)
                    exportHandler.updateCellValue("B", 97, 1);
                else
                    exportHandler.updateCellValue("B", 97, 2);

                if (csht.GAN_CHO_TRUNG_TAM == 1)
                    exportHandler.updateCellValue("B", 98, 1);
                else
                    exportHandler.updateCellValue("B", 98, 2);

                if (csht.GAN_CO_SO_GIAI_TRI == 1)
                    exportHandler.updateCellValue("B", 99, 1);
                else
                    exportHandler.updateCellValue("B", 99, 2);

                if (csht.GAN_DICH_VU_CONG_CONG == 1)
                    exportHandler.updateCellValue("B", 100, 1);
                else
                    exportHandler.updateCellValue("B", 100, 2);

                if (csht.GAN_THONG_TIN_LIEN_LAC == 1)
                    exportHandler.updateCellValue("B", 101, 1);
                else
                    exportHandler.updateCellValue("B", 101, 2);
            }
            #endregion update csht
            

            
            #region update ctxd
           
            if (ctxd != null)
            {
                // Update loai nha
                if (ctxd.LOAI_NHA == 23)
                    exportHandler.updateCellValue("B", 34, 1);                    //lay tu bang THAM SO: biet thu
                else if (ctxd.LOAI_NHA == 24)
                    exportHandler.updateCellValue("B", 34, 2);                     //lay tu bang THAM SO: pho
                else if (ctxd.LOAI_NHA == 25)
                    exportHandler.updateCellValue("B", 34, 3);                    //lay tu bang THAM SO: lien ke
                else if (ctxd.LOAI_NHA == 26)
                    exportHandler.updateCellValue("B", 34, 4);                   //lay tu bang THAM SO: chung cu
                else if (ctxd.LOAI_NHA == 27)                                   //lay tu bang THAM SO: khac
                {
                    exportHandler.updateCellValue("B", 34, ctxd.NOI_DUNG_LOAI_NHA);
                }

                //Update cấp nhà
                if (ctxd.LOAI_CAP_NHA == 28)
                    exportHandler.updateCellValue("B", 35, 1);                 //lay tu bang THAM SO: cap 1
                else if (ctxd.LOAI_CAP_NHA == 29)
                    exportHandler.updateCellValue("B", 35, 2);                 //lay tu bang THAM SO: cap 2
                else if (ctxd.LOAI_CAP_NHA == 30)
                    exportHandler.updateCellValue("B", 35, 3);                 //lay tu bang THAM SO: cap 3
                else if (ctxd.LOAI_CAP_NHA == 31)
                    exportHandler.updateCellValue("B", 35, 4);                 //lay tu bang THAM SO: cap 4
                else if (ctxd.LOAI_CAP_NHA == 32)
                    exportHandler.updateCellValue("B", 35, 5);                 //lay tu bang THAM SO: khac

                // Update Huong nha
                if (ctxd.HUONG_NHA == 33)
                    exportHandler.updateCellValue("B", 36, 1);                 //lay tu bang THAM SO: D
                else if (ctxd.HUONG_NHA == 34)
                    exportHandler.updateCellValue("B", 36, 2);                 //lay tu bang THAM SO: DB
                else if (ctxd.HUONG_NHA == 35)
                    exportHandler.updateCellValue("B", 36, 3);                 //lay tu bang THAM SO: T
                else if (ctxd.HUONG_NHA == 36)
                    exportHandler.updateCellValue("B", 36, 4);                 //lay tu bang THAM SO: TB
                if (ctxd.HUONG_NHA == 37)
                    exportHandler.updateCellValue("B", 36, 5);                 //lay tu bang THAM SO: N
                else if (ctxd.HUONG_NHA == 38)
                    exportHandler.updateCellValue("B", 36, 6);                 //lay tu bang THAM SO: DN
                else if (ctxd.HUONG_NHA == 39)
                    exportHandler.updateCellValue("B", 36, 7);                 //lay tu bang THAM SO: B
                else if (ctxd.HUONG_NHA == 40)
                    exportHandler.updateCellValue("B", 36, 8);                 //lay tu bang THAM SO: TN

                // Update giay phep xay dung
                if (ctxd.GIAY_PHEP_XAY_DUNG == "Y")
                    exportHandler.updateCellValue("B", 37, 1);
                else if (ctxd.GIAY_PHEP_XAY_DUNG == "N")
                    exportHandler.updateCellValue("B", 37, 2);

                // Update Dien tich san xay dung
                exportHandler.updateCellValue("B", 39, ctxd.DIEN_TICH_XD_CONG_NHAN);
                exportHandler.updateCellValue("B", 40, ctxd.DIEN_TICH_XD_KHONG_CONG_NHAN);

                //update Nam xay dung xong
                exportHandler.updateCellValue("B", 67, ctxd.NAM_XAY_DUNG);

                exportHandler.updateCellValue("B", 68, ctxd.SO_NAM_SU_DUNG);

                exportHandler.updateCellValue("B", 69, ctxd.DON_GIA_XAY_DUNG);

                exportHandler.updateCellValue("B", 70, ctxd.TUOI_DOI_KINH_TE);

                exportHandler.updateCellValue("B", 71, ctxd.SO_LAN_SUA_CHUA);
            }
            #endregion update ctxd
            
            
            #region update vlxd
            if (vlxd != null)
            {
                //Update vat lieu xay dung
                if (vlxd.LOAI_KET_CAU_KHUNG == 41)
                    exportHandler.updateCellValue("B", 42, 1);                 //lay tu bang THAM SO: BTCT
                else if (vlxd.LOAI_KET_CAU_KHUNG == 42)
                    exportHandler.updateCellValue("B", 42, 2);              //lay tu bang THAM SO: Gach
                else if (vlxd.LOAI_KET_CAU_KHUNG == 43)
                    exportHandler.updateCellValue("B", 42, 3);                 //lay tu bang THAM SO: Go
                else if (vlxd.LOAI_KET_CAU_KHUNG == 44)
                {
                    exportHandler.updateCellValue("B", 42, vlxd.NOI_DUNG_KET_CAU_KHUNG);
                }

                if (vlxd.LOAI_KET_CAU_MONG == 45)
                    exportHandler.updateCellValue("B", 43, 1);                 //lay tu bang THAM SO: Coc
                else if (vlxd.LOAI_KET_CAU_MONG == 46)
                    exportHandler.updateCellValue("B", 43, 2);                 //lay tu bang THAM SO: Bang
                else if (vlxd.LOAI_KET_CAU_MONG == 47)
                    exportHandler.updateCellValue("B", 43, 3);                 //lay tu bang THAM SO: Be
                else if (vlxd.LOAI_KET_CAU_MONG == 48)
                {
                    exportHandler.updateCellValue("B", 43, vlxd.NOI_DUNG_KET_CAU_MONG);
                }

                if (vlxd.LOAI_KET_CAU_TUONG_NHA == 49)
                    exportHandler.updateCellValue("B", 44, 1);                 //lay tu bang THAM SO: Gacg
                else if (vlxd.LOAI_KET_CAU_TUONG_NHA == 50)
                    exportHandler.updateCellValue("B", 44, 2);                 //lay tu bang THAM SO: Go
                else if (vlxd.LOAI_KET_CAU_TUONG_NHA == 51)
                    exportHandler.updateCellValue("B", 44, 3);                 //lay tu bang THAM SO: Tole
                else if (vlxd.LOAI_KET_CAU_TUONG_NHA == 52)
                {
                    exportHandler.updateCellValue("B", 44, vlxd.NOI_DUNG_KET_CAU_TUONG_NHA);
                }

                if (vlxd.LOAI_KET_CAU_NEN == 53)
                    exportHandler.updateCellValue("B", 45, 1);                 //lay tu bang THAM SO: Gacg
                else if (vlxd.LOAI_KET_CAU_NEN == 54)
                    exportHandler.updateCellValue("B", 45, 2);
                //lay tu bang THAM SO: Go
                else if (vlxd.LOAI_KET_CAU_NEN == 55)
                    exportHandler.updateCellValue("B", 45, 3);                 //lay tu bang THAM SO: Xi mang
                else if (vlxd.LOAI_KET_CAU_NEN == 56)
                {
                    exportHandler.updateCellValue("B", 45, vlxd.NOI_DUNG_KET_CAU_NEN);
                }

                if (vlxd.LOAI_KET_CAU_MAI == 57)
                    exportHandler.updateCellValue("B", 46, 1);                 //lay tu bang THAM SO: Bang
                else if (vlxd.LOAI_KET_CAU_MAI == 58)
                    exportHandler.updateCellValue("B", 46, 2);                 //lay tu bang THAM SO: Ngoi
                else if (vlxd.LOAI_KET_CAU_MAI == 59)
                    exportHandler.updateCellValue("B", 46, 3);                 //lay tu bang THAM SO: Tole
                else if (vlxd.LOAI_KET_CAU_MAI == 60)
                {
                    exportHandler.updateCellValue("B", 46, vlxd.NOI_DUNG_KET_CAU_MAI);
                }

                //Update danh gia chat luong con lai
                exportHandler.updateCellValue("B", 48, vlxd.CHAT_LUONG_MONG);
                exportHandler.updateCellValue("B", 49, vlxd.CHAT_LUONG_SAN);
                exportHandler.updateCellValue("B", 50, vlxd.CHAT_LUONG_TUONG);
                exportHandler.updateCellValue("B", 51, vlxd.CHAT_LUONG_TRAN);
                exportHandler.updateCellValue("B", 52, vlxd.CHAT_LUONG_MAI);

                // Update Ti le ket cau chinh
                exportHandler.updateCellValue("B", 54, vlxd.TI_LE_KET_CAU_MONG);
                exportHandler.updateCellValue("B", 55, vlxd.TI_LE_KET_CAU_SAN);
                exportHandler.updateCellValue("B", 56, vlxd.TI_LE_KET_CAU_TUONG);
                exportHandler.updateCellValue("B", 57, vlxd.TI_LE_KET_CAU_TRAN);
                exportHandler.updateCellValue("B", 58, vlxd.TI_LE_KET_CAU_MAI);

                exportHandler.updateCellValue("B", 59, vlxd.SO_PHONG_NGU);
                exportHandler.updateCellValue("B", 60, vlxd.SO_NHA_VE_SINH);
                exportHandler.updateCellValue("B", 59, vlxd.SO_PHONG_NGU);

                //Update trang tri noi that
                if (vlxd.LOAI_TRANG_TRI_NOI_THAT == 61)
                    exportHandler.updateCellValue("B", 61, 1);                 //lay tu bang THAM SO: cao cap
                else if (vlxd.LOAI_TRANG_TRI_NOI_THAT == 62)
                    exportHandler.updateCellValue("B", 61, 2);                 //lay tu bang THAM SO: kha
                else if (vlxd.LOAI_TRANG_TRI_NOI_THAT == 63)
                    exportHandler.updateCellValue("B", 61, 3);                 //lay tu bang THAM SO: trung binh
                else if (vlxd.LOAI_TRANG_TRI_NOI_THAT == 64)
                {
                    exportHandler.updateCellValue("B", 61, vlxd.NOI_DUNG_TRANG_TRI_NOI_THAT);                 //lay tu bang THAM SO: tam
                }

                //Update cong tring xay dung
                if (vlxd.LOAI_CONG_TRINH_XAY_DUNG == 65)
                    exportHandler.updateCellValue("B", 63, "có");                 //lay tu bang THAM SO: san thuong
                else
                    exportHandler.updateCellValue("B", 63, "Không");

                if (vlxd.LOAI_CONG_TRINH_XAY_DUNG == 66)
                    exportHandler.updateCellValue("B", 64, "có");                 //lay tu bang THAM SO: ho boi
                else
                    exportHandler.updateCellValue("B", 64, "Không");

                if (vlxd.LOAI_CONG_TRINH_XAY_DUNG == 67)
                    exportHandler.updateCellValue("B", 65, "có");                 //lay tu bang THAM SO: san
                else
                    exportHandler.updateCellValue("B", 65, "Không");

                if (vlxd.LOAI_CONG_TRINH_XAY_DUNG == 68)
                {
                    exportHandler.updateCellValue("B", 66, vlxd.NOI_DUNG_CONG_TRINH_XAY_DUNG);
                }
            }
            #endregion update vlxd
            
            exportHandler.updateCellValue("B", 107, "Nguoi khao sat");

            if(hstd != null)
                exportHandler.renameCurrentSheet("Hồ sơ " + hstd.SO_PHIEU);

            exportHandler.endEditting();

            #endregion Process excel

            // write destination file finish and begin to convert file to byte array, then send it to client
            if (File.Exists(exportFilePath))
            {
                byteArr = WindowsFileAccess.readFileToByte(exportFilePath);
                File.Delete(exportFilePath);
            }

            return byteArr;
        }

        public byte[] exportToTotallExcel(List<HoSoThamDinhInfo> lshs)
        {
            return null;
        }

        public string exportToTotallExcelWithFilePath(List<HoSoThamDinhInfo> lshs, String searchKey)
        {
            String reportTHFileName = "";
            try
            {
                /*
             * return the name of the file
             * don't delete export file
             */

                // invalid data in
                if (lshs == null || lshs.Count == 0)
                    return null;

                reportTHFileName = copyNewFileFromTongHopTemplate(searchKey);
                // check invalid file name
                if (reportTHFileName == null)
                    return null;

                //Start processing report
                SpreedEdit exportHandler = new SpreedEdit(reportTHFileName);
                if (!exportHandler.startEditSpreed(ConfigurationManager.AppSettings["ExcelTongHopMainSheet"]))
                    return null;

                //add search information
                searchKey.Replace("||", ";");
                exportHandler.updateCellValue(totalReportMapRow["SEARCH_KEY"].ToString(), 1, "Địa chỉ thống kê:" + searchKey);

                int iCurrentColumn = TOTAL_REPORT_START_INDEX;
                foreach (HoSoThamDinhInfo hs in lshs)
                {

                    GIA_THAM_DINH gtd;
                    THUA_DAT td;
                    HO_SO_THAM_DINH hstd;
                    CO_SO_HA_TANG csht;
                    CONG_TRINH_XAY_DUNG ctxd;
                    try
                    {
                        gtd = ThamDinhGiaFacade.GetGIA_THAM_DINHBL().GetByMA_HO_SO(Int64.Parse(hs.MA_HO_SO));
                        td = ThamDinhGiaFacade.GetTHUA_DATBL().GetByMA_HO_SO(Int64.Parse(hs.MA_HO_SO));
                        hstd = ThamDinhGiaFacade.GetHO_SO_THAM_DINHBL().GetByMA_HO_SO(Int64.Parse(hs.MA_HO_SO));
                        csht = ThamDinhGiaFacade.GetCO_SO_HA_TANGBL().GetByMA_HO_SO(Int64.Parse(hs.MA_HO_SO));
                        ctxd = ThamDinhGiaFacade.GetCONG_TRINH_XAY_DUNGBL().GetByMA_HO_SO(Int64.Parse(hs.MA_HO_SO));
                    }
                    catch (Exception e)
                    {
                        continue;
                    }

                    if (gtd == null || td == null || hstd == null)
                    {
                        continue;
                    }

                    String sCurrentColumn = GetExcelColumnName(iCurrentColumn);

                    #region excelprocess
                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["YEU_TO_SO_SANH"].ToString()), YEU_TO_SO_SANH + (iCurrentColumn - TOTAL_REPORT_START_INDEX + 1).ToString());
                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["DIA_CHI"].ToString()), hs.DIA_CHI);
                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["GIA_BAN"].ToString()), gtd.GIA_GIAO_DICH_TRIEU_DONG);
                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["DTKV"].ToString()), td.TONG_DIEN_TICH);
                    if (hs.GIA_DAT_BINH_QUAN_1 > 0)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["GIA_DAT_TLKC"].ToString()), hs.GIA_DAT_BINH_QUAN_1 * iMoneyUnitRate);
                    if (hs.GIA_DAT_BINH_QUAN_2 > 0)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["GIA_DAT_TDCT"].ToString()), hs.GIA_DAT_BINH_QUAN_2 * iMoneyUnitRate);
                    switch (gtd.LOAI_THANH_TOAN)
                    {
                        case 7:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["DK_THANH_TOAN"].ToString()), "Trả ngay");
                            break;
                        case 8:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["DK_THANH_TOAN"].ToString()), "Trả chậm");
                            break;
                        case 9:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["DK_THANH_TOAN"].ToString()), gtd.NOI_DUNG_THANH_TOAN);
                            break;
                        default:
                            break;
                    }

                    // add tinh trang phap li
                    switch (hstd.TINH_TRANG_PHAP_LY)
                    {
                        case 3:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["TT_PHAP_LI"].ToString()), "Giấy Hường");
                            break;
                        case 4:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["TT_PHAP_LI"].ToString()), "Giấy Đỏ");
                            break;
                        case 5:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["TT_PHAP_LI"].ToString()), "Giấy Hồng");
                            break;
                        case 6:
                            exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["TT_PHAP_LI"].ToString()), hstd.NOI_DUNG_PHAP_LY);
                            break;
                    }

                    // add vị tri
                    String sVitri = "";
                    String newLine = "\n";

                    if (td.LOAI_DUONG_PHO == 14) // mat tien
                    {
                        sVitri += "Nhà mặt tiền:";
                        sVitri += newLine;
                        sVitri += "Lộ giới " + td.LO_GIOI + "m,";
                        sVitri += newLine;
                        sVitri += "Số mặt tiền:" + td.SO_MAT_TIEN + ",";
                        sVitri += newLine;

                        if (td.LOAI_LAN_DUONG == 16)
                            sVitri += "Đường một chiều.";            //lay tu bang tham so : mot chieu
                        else if (td.LOAI_LAN_DUONG == 17)
                            sVitri += "Đường hai chiều.";            //lay tu bang tham so : hai chieu
                        else if (td.LOAI_LAN_DUONG == 18)
                            sVitri += "Đường hai chiều có dải phân cách.";             //lay tu bang tham so : co dai phan cach
                    }
                    else if (td.LOAI_DUONG_PHO == 15)
                    {
                        if (td.LOAI_HEM == 19)
                            sVitri += "Hẻm cấp 1-Cấp 1";             //lay tu bang tham so : hem cap 1
                        else if (td.LOAI_HEM == 20)
                            sVitri += "Hẻm cấp 1-Cấp 2";             //lay tu bang tham so : hem cap 2
                        else if (td.LOAI_HEM == 21)
                            sVitri += "Hẻm cấp 1-Cấp 3";             //lay tu bang tham so : hem cap 3
                        else if (td.LOAI_HEM == 22)
                            sVitri += "Hẻm cấp 1-Cấp 4";             //lay tu bang tham so : hem cap 4

                        if (td.LOAI_HEM == 23)
                            sVitri += "Hẻm cấp khác-Cấp 1";            //tu dinh nghia, k co trong bang tham so: hem cap khac cap 1
                        else if (td.LOAI_HEM == 24)
                            sVitri += "Hẻm cấp khác-Cấp 2";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 2
                        else if (td.LOAI_HEM == 25)
                            sVitri += "Hẻm cấp khác-Cấp 3";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 3
                        else if (td.LOAI_HEM == 26)
                            sVitri += "Hẻm cấp khác-Cấp 4";             //tu dinh nghia, k co trong bang tham so: hem cap khac cap 4

                    }
                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["VI_TRI"].ToString()), sVitri);

                    // add lợi thế kinh doanh
                    if (hstd.LOAI_KHU_VUC_KINH_DOANH == 0) // khong co
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["LOI_THE_KD"].ToString()), "Không có");
                    else if (hstd.LOAI_KHU_VUC_KINH_DOANH == 1) // trung binh
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["LOI_THE_KD"].ToString()), "Trung bình");
                    else if (hstd.LOAI_KHU_VUC_KINH_DOANH == 2) // sam uat
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["LOI_THE_KD"].ToString()), "Sầm uất");


                    // add hình dạng thửa đất
                    if (td.LOAI_HINH_DANG_THUA_DAT == 10) //lay tu bang tham so : Vuong
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HD_THUA_DAT"].ToString()), "Vuông");
                    else if (td.LOAI_HINH_DANG_THUA_DAT == 11) //lay tu bang tham so : Top
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HD_THUA_DAT"].ToString()), "Tốp");
                    else if (td.LOAI_HINH_DANG_THUA_DAT == 12) //lay tu bang tham so : No hau
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HD_THUA_DAT"].ToString()), "Nở hậu");
                    else if (td.LOAI_HINH_DANG_THUA_DAT == 13)  //lay tu bang tham so : Khac
                    {
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HD_THUA_DAT"].ToString()), td.HINH_DANG_THUA_DAT);
                    }

                    //add cơ sở hạ tầng

                    String sCsht = "Cơ sở hạ tầng kỉ thuật:";
                    sCsht += newLine;
                    if (csht.DONG_HO_DIEN == 1)
                        sCsht += "Dồng hồ điện: Có.";
                    else
                        sCsht += "Dồng hồ điện: Không.";

                    sCsht += newLine;

                    if (csht.CAP_TREO == 1)
                        sCsht += "Cáp treo: Có.";
                    else
                        sCsht += "Cáp treo: Không.";

                    sCsht += newLine;

                    if (csht.CAP_NGAM == 1)
                        sCsht += "Cáp ngầm: Có.";
                    else
                        sCsht += "Cáp ngầm: Không.";

                    sCsht += newLine;

                    if (csht.DONG_HO_NUOC == 1)
                        sCsht += "Đồng hồ nước: Có.";
                    else
                        sCsht += "Đồng hồ nước: Không.";

                    sCsht += newLine;

                    if (csht.AP_LUC_NUOC == 1)
                        sCsht += "Áp lực nước: Mạnh.";
                    else
                        sCsht += "Áp lực nước: Yếu.";

                    sCsht += newLine;

                    if (csht.CHAT_LUONG_NUOC == 1)
                        sCsht += "Chất lượng nước: Sạch.";
                    else
                        sCsht += "Chất lượng nước: Dơ.";

                    sCsht += newLine;

                    if (csht.TIENG_ON == 1)
                        sCsht += "Tiếng ồn: Có.";
                    else
                        sCsht += "Tiếng ồn: Không.";

                    sCsht += newLine;

                    if (csht.KHOI_BUI == 1)
                        sCsht += "Khói bụi: Nhiều.";
                    else if (csht.KHOI_BUI == 2)
                        sCsht += "Khói bụi: Trung bình.";
                    else
                        sCsht += "Khói bụi: Ít.";

                    sCsht += newLine;

                    if (csht.TINH_TRANG_NGAP == 1)
                        sCsht += "Tình trạng ngập: Nhiều.";
                    else if (csht.TINH_TRANG_NGAP == 2)
                        sCsht += "Tình trạng ngập: Trung bình.";
                    else if (csht.TINH_TRANG_NGAP == 3)
                        sCsht += "Tình trạng ngập: Ít.";
                    else
                        sCsht += "Tình trạng ngập: Không có.";

                    sCsht += newLine;

                    if (csht.HE_THONG_CHIEU_SANG == 1)
                        sCsht += "Chiếu sáng vỉa hè: Có.";
                    else
                        sCsht += "Chiếu sáng vỉa hè: Không.";

                    sCsht += newLine;

                    if (csht.CHOT_BAO_VE == 1)
                        sCsht += "Chốt bảo vệ: Có.";
                    else
                        sCsht += "Chốt bảo vệ: Không.";

                    sCsht += newLine;

                    if (csht.AN_NINH == 1)
                        sCsht += "Anh ninh: Tốt.";
                    else if (csht.AN_NINH == 2)
                        sCsht += "Anh ninh: Trung bình.";
                    else
                        sCsht += "Anh ninh: Kém.";

                    sCsht += newLine;
                    sCsht += "Cơ sở hạ tầng xã hội:";
                    sCsht += newLine;

                    if (csht.GAN_CO_SO_GIAO_DUC == 1)
                        sCsht += "Gần Cơ sở giáo dục: Có";
                    else
                        sCsht += "Gần cơ sở giáo dục: Không";

                    sCsht += newLine;

                    if (csht.GAN_CO_SO_Y_TE == 1)
                        sCsht += "Gần cơ sở y tế: Có";
                    else
                        sCsht += "Gần cơ sở y tế: Không";

                    sCsht += newLine;

                    if (csht.GAN_CHO_TRUNG_TAM == 1)
                        sCsht += "Gần chợ trung tâm: Có";
                    else
                        sCsht += "Gần chợ trung tâm: Không";

                    sCsht += newLine;

                    if (csht.GAN_CO_SO_GIAI_TRI == 1)
                        sCsht += "Gần cơ sở giải trí: Có";
                    else
                        sCsht += "Gần cơ sở giải trí: Không";

                    sCsht += newLine;

                    if (csht.GAN_DICH_VU_CONG_CONG == 1)
                        sCsht += "Gần DV công cộng: Có";
                    else
                        sCsht += "Gần DV công cộng: Không";

                    sCsht += newLine;

                    if (csht.GAN_THONG_TIN_LIEN_LAC == 1)
                        sCsht += "Gần TT Liên lạc: Có";
                    else
                        sCsht += "Gần TT liên lạc: Không";

                    exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["CS_HA_TANG"].ToString()), sCsht);

                    // Update Huong nha
                    if (ctxd.HUONG_NHA == 33)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Đông");                 //lay tu bang THAM SO: D
                    else if (ctxd.HUONG_NHA == 34)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Đông bắc");                  //lay tu bang THAM SO: DB
                    else if (ctxd.HUONG_NHA == 35)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Tây");                  //lay tu bang THAM SO: T
                    else if (ctxd.HUONG_NHA == 36)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Tây bắc");                  //lay tu bang THAM SO: TB
                    if (ctxd.HUONG_NHA == 37)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Tây nam");                  //lay tu bang THAM SO: N
                    else if (ctxd.HUONG_NHA == 38)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Đông nam");                  //lay tu bang THAM SO: DN
                    else if (ctxd.HUONG_NHA == 39)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Bắc");                  //lay tu bang THAM SO: B
                    else if (ctxd.HUONG_NHA == 40)
                        exportHandler.updateCellValue(sCurrentColumn, Int32.Parse(totalReportMapRow["HUONG"].ToString()), "Tây nam");                  //lay tu bang THAM SO: TN

                    #endregion excelprocess

                    // go next column
                    iCurrentColumn++;
                }
                //exportHandler.borderCellBlock(ConfigurationManager.AppSettings["ExcelTongHopMainSheet"], TOTAL_REPORT_START_INDEX, 2, TOTAL_REPORT_START_INDEX + lshs.Count, 50);
                exportHandler.endEditting();
                
            }
            catch (Exception exc)
            {
                throw;
            }
            
            return reportTHFileName;
        }

        public static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        private String getTongHopTemplateFilePath()
        {
            return (ConfigurationManager.AppSettings["ExcelTongHopExportFilePath"] + ConfigurationManager.AppSettings["ExcelTongHopTemplateFileName"]);
        }

        private String copyNewFileFromTongHopTemplate(String searchKey)
        {
            String templateFile = getTongHopTemplateFilePath();
            templateFile = HttpContext.Current.Server.MapPath(templateFile);
            if (!File.Exists(templateFile))
                return null;
            String exportFolder = ConfigurationManager.AppSettings["ExcelTongHopExportDesPath"];
            _filename = Guid.NewGuid().ToString();

            // Add search key information
            string[] searchKeys = searchKey.Split(SEARCH_KEY_TOKEN.ToCharArray());
            if (searchKeys != null)
            {
                foreach (string str in searchKeys)
                {
                    _filename += "_";
                    _filename += str;
                }
            }

            String finalFilePath = exportFolder + _filename;

            finalFilePath = HttpContext.Current.Server.MapPath(finalFilePath);
            File.Copy(templateFile, finalFilePath);

            if (File.Exists(finalFilePath))
                return finalFilePath;
            return null;
        }

        private String copyNewFileFromBKSTemplate()
        {
            //make a excel file by copy and rename from template file
            String templateFile = getBKSTemplateFilePath();
            templateFile = HttpContext.Current.Server.MapPath(templateFile);

            if (!File.Exists(templateFile))
                return null;
            String exportFolder = ConfigurationManager.AppSettings["ExcelExportDes"];
            _filename = Guid.NewGuid().ToString();
            String finalFilePath = exportFolder + _filename;
            finalFilePath = HttpContext.Current.Server.MapPath(finalFilePath);
            File.Copy(templateFile, finalFilePath);

            if (File.Exists(finalFilePath))
                return finalFilePath;
            return null;
        }

        private String getBKSTemplateFilePath()
        {
            return (ConfigurationManager.AppSettings["ExcelTemplateFilePath"] + ConfigurationManager.AppSettings["ExcelTemplateFileName"]);
        }

        #region ExportGiaDoanDuong

        public String exportGiaDoanDuong()
        {
            // get danh sach cac doan duong
            DuongKhaoSatDataService dksDS = new DuongKhaoSatDataService();
            List<DuongKhaoSatInfo> dsDKS = dksDS.searchDuong4GiaDat();

            //Pharse 2 convert to old data model
            //List<DUONG_KHAO_SAT> listDKS = convertListDKSInfor2DUONG_KHAOSAT(dsDKS);

            List<DUONG_KHAO_SAT> listDKS = new List<DUONG_KHAO_SAT>();

            List<GIADOANDUONG_QUAN> Quans = extractData(dsDKS);

            // get all gia cac doan duong
            GiaDoanDuongDataService gddDS = new GiaDoanDuongDataService();


            // can cu vao quan + duong de tim gia doan duong

            foreach (GIADOANDUONG_QUAN quan in Quans)
            {
                quan.isValid = false;
                foreach (GIADOANDUONG_DKS duong in quan.DUONG)
                {
                    //String tenQuan = ThamDinhGiaFacade.GetTHAM_SOBL().GetByMA_THAM_SO(quan.QUAN).NOI_DUNG;
                    DataSet ds = gddDS.getListGiaDoanDuong(quan.QUAN.ToString(), duong.TEN_DUONG);
                    duong.DOAN_DUONG = getGDDFromDataSet(ds);
                    if (duong.DOAN_DUONG.Count == 0)
                    {
                        duong.isValid = false;
                    }
                    else
                    {
                        
                        quan.isValid = true;
                        duong.isValid = true;
                    }
                }
            }

            // adapting with old code: remove quan va duong khong co doanduong
            int numQuan = Quans.Count;
            for (int i = Quans.Count - 1; i >= 0; i--)
            {
                if (!Quans[i].isValid)
                {
                    Quans.RemoveAt(i);
                }
                else
                {
                    int numDuong = Quans[i].DUONG.Count;
                    for (int j = Quans[i].DUONG.Count - 1; j >= 0; j--)
                    {
                        if (!Quans[i].DUONG[j].isValid)
                        {
                            Quans[i].DUONG.RemoveAt(j);
                        }
                    }
                }

            }

            //List<GIA_DOAN_DUONG> listGiaDoanDuong = ThamDinhGiaFacade.GetGIA_DOAN_DUONGL().GetList();

            //if (listDKS == null || listGiaDoanDuong == null)
                //return null;
            



            // prepare file to export
            String exportFileName = copyNewFileFromGiaDoanDuongTemplate();

            if (exportFileName == null)
                return null;

            // parse data
            //List<GIADOANDUONG_QUAN> Quans = parseGiaDoanDuongData(listDKS, listGiaDoanDuong);
            
            if (Quans == null)
                return exportFileName; 
            
            // Xuất...
            SpreedEdit excelHandeler = new SpreedEdit(exportFileName);

            if (excelHandeler == null)
                return null;

            foreach (GIADOANDUONG_QUAN quan in Quans)
            {
                quan.TinhMaxSoLuongGia();
                THAM_SO ts = ThamDinhGiaFacade.GetTHAM_SOBL().GetByMA_THAM_SO(quan.QUAN);
                if (!excelHandeler.createNewWorkSheet(ConfigurationManager.AppSettings["ExcelGiaDoanDuongMainSheet"], ts.NOI_DUNG))
                    continue;
                else
                {
                    excelHandeler.startEditSpreed(ts.NOI_DUNG);
                    if (!excelHandeler.initCellsBlockWithStyle(ts.NOI_DUNG, 1, 7, 3 + quan.MAX_SOLUONG_GIA_QUAN, quan.EndDataRow - 1, excelHandeler.formatIndexs.iThinBorderIndex))
                    {
                        excelHandeler.endEditting();
                        continue;
                    }
                    excelHandeler.updateCellValue("A", 4, "BẢNG GIÁ ĐẤT BÌNH QUÂN." + "\n" + "Quận/Huyện:" + ts.NOI_DUNG);
                    quan.XuatGiaDoanDuong_QUAN(excelHandeler);
                    //quan.CapNhatNhanCotGia(excelHandeler);
                    //excelHandeler.borderCellBlock(ts.NOI_DUNG, 1, 7, 3 + quan.MAX_SOLUONG_GIA_QUAN, totalRow - 1);
                    quan.MergeCellDuong(excelHandeler);
                    excelHandeler.endEditting();

                    //excelHandeler.borderCellBlock(ts.NOI_DUNG, "A7", GetExcelColumnName(3 + quan.MAX_SOLUONG_GIA_QUAN) + (totalRow - 1));
                    //excelHandeler.FillBackgroundColor(ts.NOI_DUNG, "A7", GetExcelColumnName(quan.MAX_SOLUONG_GIA_QUAN + 3) + "7");
                }
            }
            if (Quans.Count > 0)
                excelHandeler.DeleteAWorkSheet(ConfigurationManager.AppSettings["ExcelGiaDoanDuongMainSheet"]);
            else
            {
                excelHandeler.startEditSpreed(ConfigurationManager.AppSettings["ExcelGiaDoanDuongMainSheet"]);
                excelHandeler.updateCellValue("A", 4, "Không có dữ liệu về giá đất của đoạn đường");
                excelHandeler.endEditting();
            }
            return exportFileName;
        }

        private String getGiaDoanDuongTemplateFilePath()
        {
            return (ConfigurationManager.AppSettings["ExcelGiaDoanDuongExportFilePath"] + ConfigurationManager.AppSettings["ExcelGiaDoanDuongTemplateFileName"]);
        }

        private String copyNewFileFromGiaDoanDuongTemplate()
        {
            String templateFile = getGiaDoanDuongTemplateFilePath();
            templateFile = HttpContext.Current.Server.MapPath(templateFile);
            if (!File.Exists(templateFile))
                return null;
            String exportFolder = ConfigurationManager.AppSettings["ExcelGiaDoanDuongExportDesPath"];
            _filename = Guid.NewGuid().ToString();

            // Add Export Day information
            DateTime curDate = new DateTime();
            curDate = DateTime.Now;

            //_filename += (ConfigurationManager.AppSettings["ExcelGiaDoanDuongExportFileName"] + "_" + curDate.Day + "_" + curDate.Month + "_" + curDate.Year + ".xlsx");
            _filename += (ConfigurationManager.AppSettings["ExcelGiaDoanDuongExportFileName"] + curDate.Day + "_" + curDate.Month + "_" + curDate.Year);

            String finalFilePath = exportFolder + _filename;

            finalFilePath = HttpContext.Current.Server.MapPath(finalFilePath);
            File.Copy(templateFile, finalFilePath);

            if (File.Exists(finalFilePath))
                return finalFilePath;
            return null;
        }

        private List<GIADOANDUONG_QUAN> parseGiaDoanDuongData(List<DUONG_KHAO_SAT> dks, List<GIA_DOAN_DUONG> gia)
        {
            List<GIADOANDUONG_QUAN> rv = new List<GIADOANDUONG_QUAN>();

            
            foreach (GIA_DOAN_DUONG giadd in gia)
            {
                foreach (DUONG_KHAO_SAT duong in dks)
                {
                    if (duong.MA_DKS == giadd.MA_DKS)
                    {
                        int iQuanExist = checkQuanExist(rv, duong);
                        //quan chua duoc luu
                        if (iQuanExist == -1)
                        {
                            GIADOANDUONG_QUAN quan = new GIADOANDUONG_QUAN(duong, giadd);
                            rv.Add(quan);
                            break;
                        }
                        else
                        {
                            rv[iQuanExist].XuLiQuanDaTonTai(duong, giadd);
                        }
                    }
                }
            }

            return rv;
        }

        private int checkQuanExist(List<GIADOANDUONG_QUAN> data, DUONG_KHAO_SAT dks)
        {
            int iQuan = -1;
            if(data == null)
                return iQuan;
            for (int i = 0; i < data.Count; i++)
            {
                if (data[i].QUAN == dks.QUAN)
                    iQuan = i;
            }

            return iQuan;
        }

        // Convert List<DuongKhaoSatInfo> to List<DUONG_KHAO_SAT>
        private List<DUONG_KHAO_SAT> convertListDKSInfor2DUONG_KHAOSAT(List<DuongKhaoSatInfo> input)
        {
            List<DUONG_KHAO_SAT> rv = new List<DUONG_KHAO_SAT>();
            foreach (DuongKhaoSatInfo dsk in input)
            {
                rv.Add(convertDKSInfor2DUONG_KHAOSAT(dsk));
            }
            return rv;
        }

        private DUONG_KHAO_SAT convertDKSInfor2DUONG_KHAOSAT(DuongKhaoSatInfo input)
        {
            DUONG_KHAO_SAT rv = new DUONG_KHAO_SAT();
            if(input == null)
                return null;
            rv.ACTIVE_FLAG = input.ACTIVE_FLAG;
            rv.DOAN_DUONG = input.DOAN_DUONG;
            rv.DUONG = input.DUONG;
            rv.MA_DKS = input.MA_DKS;
            rv.NGAY_KHOI_TAO = input.NGAY_KHOI_TAO;
            rv.PHUONG = input.PHUONG;
            rv.QUAN = input.QUAN;
            return rv;
        }

        //Extract ds gia doan duong tu danh sach duong khao sat
        private List<GIADOANDUONG_QUAN> extractData(List<DuongKhaoSatInfo> input)
        {
            List<GIADOANDUONG_QUAN> rv = new List<GIADOANDUONG_QUAN>();
            if (input == null)
                return null;

            // Lay danh sach quan
            #region Lay danh sach quan
            int curQuan = -1;
            // Duyet danh sach duong
            foreach (DuongKhaoSatInfo dd in input)
            {
                // Neu quan chua duoc them vao -> them quan + them duong tuong dung vao quan
                if (!dd.QUAN.Equals(curQuan))
                {
                    GIADOANDUONG_QUAN quan = new GIADOANDUONG_QUAN();
                    quan.QUAN = dd.QUAN;
                    GIADOANDUONG_DKS duong = new GIADOANDUONG_DKS();
                    duong.TEN_DUONG = dd.DUONG;
                    quan.AddDuong(duong);
                    curQuan = dd.QUAN;
                    rv.Add(quan);
                }
                else
                {
                    GIADOANDUONG_DKS duong = new GIADOANDUONG_DKS();
                    duong.TEN_DUONG = dd.DUONG;
                    rv[rv.Count - 1].AddDuong(duong);
                }
            }
            #endregion



            return rv;
        }

        private List<GIADOANDUONG_GDD> getGDDFromDataSet(DataSet input)
        {
            List<GIADOANDUONG_GDD> rv = new List<GIADOANDUONG_GDD>();
            if (input == null)
                return null;
            DataTable table = input.Tables[0];
            foreach(DataRow dr in table.Rows)
            {
                
                if(dr[5] == null)
                    continue;
                GIADOANDUONG_GDD doanduong = new GIADOANDUONG_GDD();
                doanduong.TEN_DOAN_DUONG = dr[4].ToString();
                String gia = dr[6].ToString();
                doanduong.GIA = extractGiaDoanDuong(gia);
                rv.Add(doanduong);
            }

            return rv;
        }

        List<GIA_DOAN_DUONG> extractGiaDoanDuong(String xmlInput)
        {
            List<GIA_DOAN_DUONG> rv = new List<GIA_DOAN_DUONG>();
            XmlDocument doc = new XmlDocument();
            doc.LoadXml(xmlInput);
            XmlNodeList prices = doc.GetElementsByTagName("item");
            String pattern = "yyyyMMdd";
            for (int i = 0; i < prices.Count; i++)
            {
                GIA_DOAN_DUONG gia = new GIA_DOAN_DUONG();
                XmlAttributeCollection attrColl = prices[i].Attributes;
                gia.GIA_DAT = Double.Parse(attrColl[0].Value);
                if (i == 0)
                {
                    DateTime parseDate;
                    DateTime.TryParseExact(attrColl[1].Value, pattern, null, DateTimeStyles.None, out parseDate);
                    if (parseDate != null)
                        gia.NGAY_KHOI_TAO = parseDate;
                }
                else
                {
                    DateTime parseDate;
                    DateTime.TryParseExact(attrColl[1].Value, pattern, null, DateTimeStyles.None, out parseDate);
                    if (parseDate != null)
                        gia.NGAY_CAP_NHAT = parseDate;
                }
                rv.Add(gia);

            }
            return rv;
        }

        DateTime convertDateTime(String input)
        {
            int Year = Int32.Parse(input.Substring(0,4));
            int Moth = Int32.Parse(input.Substring(4, 2));
            int Day = Int32.Parse(input.Substring(6, 2));
            DateTime rv = new DateTime(Year, Moth, Day);
            return rv;
        }

        #endregion ExportGiaDoanDuong
    }
}